﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using MySql.Data;
using MySql.Data.Common;
using MySql.Data.MySqlClient;
using System.Windows.Forms;


namespace Event_Planner
{
    public class DB
    {
        private MySqlConnection conn;
        private string connectionString;

        public DB()  //initializing the database connection
        {
            conn = new MySqlConnection();

            //string server = "localhost";
            string server = "athena01.fhict.local";
            string database = "dbi251908";
            string uid = "dbi251908";
            string password = "wwLxEZx7g3";

            //string server = "athena01.fhict.local";
            //string database = "dbi270138";
            //string uid = "dbi270138";
            //string password = "nvXoUi2EtK";


            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            conn.ConnectionString = connectionString;
        }

        #region shopAppFunctions
        public DataTable getAllShopsFromDB()  // get all the shops from the shop table
        {
            try
            {
                DataSet ds = new DataSet();
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter("Select * from shop",conn);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public void withdrawParticipantAccount(int accountNumber, double amount)
        {//update a participant's account when a purchase is made
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            connection.Open();

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE account SET balance=balance-@amount WHERE account_number=@account_number";
                cmd.Parameters.AddWithValue("@account_number", accountNumber);
                cmd.Parameters.AddWithValue("@amount", amount);
                cmd.ExecuteNonQuery();
                /*int id = Convert.ToInt32(cmd.LastInsertedId);
                return id;*/

            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public DataTable getAllItemsFromDB()   // get all the items from the item table
        {
            try
            {
                DataSet ds = new DataSet();
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter("Select * from item", conn);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }
        }

        public DataTable getShopItemsFromDB(int shopId)//get all the items from a specific shop with id = shopId
        {
            DataSet ds = new DataSet();
            conn.Open();
            MySqlCommand cmd;
            try
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select * from item where shop_id = @shopId";
                cmd.Parameters.AddWithValue("@shopId", shopId);
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }
        }

        public double getUserBalance(int userId)//get a user's account balance
        {
            DataSet ds = new DataSet();
            conn.Open();
            MySqlCommand cmd;
            try
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select * from participant,account WHERE participant.account_number = account.account_number";
                cmd.CommandText += " AND participant.participant_id = @userId";
                cmd.Parameters.AddWithValue("@userId", userId);
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return Convert.ToDouble(ds.Tables[0].Rows[0]["balance"].ToString());
            }
            catch (Exception exception)
            {
                //MessageBox.Show(exception.Message);
                return 0;
            }
        }

        public int getUserAccountNumber(int userId)//get a user's account number
        {
            DataSet ds = new DataSet();
            conn.Open();
            MySqlCommand cmd;
            try
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select * from participant,account WHERE participant.account_number = account.account_number";
                cmd.CommandText += " AND participant.participant_id = @userId";
                cmd.Parameters.AddWithValue("@userId", userId);
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return Convert.ToInt32(ds.Tables[0].Rows[0]["account_number"]);
            }
            catch (Exception exception)
            {
                return 0;
            }
        }

        public void insertParticipantItem(int participantId, int itemId, int quantityPurchased)
        {//when a participant buys an item, insert a new record in the participant_item table
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            connection.Open();
            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO participant_item(participant_id,item_id,quantity_purchased,purchased_date)";
                cmd.CommandText += " VALUES(@part_id,@item_id,@qty_purchased,@date)";
                cmd.Parameters.AddWithValue("@part_id", participantId);
                cmd.Parameters.AddWithValue("@item_id", itemId);
                cmd.Parameters.AddWithValue("@qty_purchased", quantityPurchased);
                cmd.Parameters.AddWithValue("@date", DateTime.Now);
                cmd.ExecuteNonQuery();
                /*int id = Convert.ToInt32(cmd.LastInsertedId);
                return id;*/

            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public void updateItemQuantity(int itemId, int quantity)
        {//update an item's stock quantity when an order is placed in the warehouse
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            connection.Open();
            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE item SET quantity=quantity-@quantity WHERE item_id=@item_id";
                cmd.Parameters.AddWithValue("@item_id", itemId);
                cmd.Parameters.AddWithValue("@quantity", quantity);
                cmd.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public int getItemQuantity(int itemId)//get an item's quantity
        {
            DataSet ds = new DataSet();
            conn.Open();
            MySqlCommand cmd;
            try
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select * from item WHERE item_id = @itemId";
                cmd.Parameters.AddWithValue("@itemId", itemId);
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return Convert.ToInt32(ds.Tables[0].Rows[0]["quantity"].ToString());
            }
            catch (Exception exception)
            {
                //MessageBox.Show(exception.Message);
                return 0;
            }
        }
        #endregion
        #region standAppFunctions

        public DataTable getAllStandsFromDB()// to get all stands from the stand table
        {
            try
            {
                DataSet ds = new DataSet();
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter("Select * from stand", conn);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>

        public DataTable getAllMaterialsFromDB() //to get all materials from the material table
        {
            try
            {
                DataSet ds = new DataSet();
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter("Select * from material", conn);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public DataTable getStandMaterialsFromDB(int standId)//to get all materials from a specific stand
        {
            DataSet ds = new DataSet();
            conn.Open();
            MySqlCommand cmd;
            try
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select * from material where stand_id = @standId";
                cmd.Parameters.AddWithValue("@standId", standId);
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public void insertParticipantMaterial(int participantId, int materialId, int quantityHired)
        {//when a participant hires a material, insert a new record into the participant_material table
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            connection.Open();

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO participant_material(participant_id,material_id,quantity_hired,return_status,hire_date)";
                cmd.CommandText += " VALUES(@part_id,@material_id,@qty_hired,@return_status,@date)";
                cmd.Parameters.AddWithValue("@part_id", participantId);
                cmd.Parameters.AddWithValue("@material_id", materialId);
                cmd.Parameters.AddWithValue("@qty_hired", quantityHired);
                cmd.Parameters.AddWithValue("@return_status", 0);
                cmd.Parameters.AddWithValue("@date", DateTime.Now);
                cmd.ExecuteNonQuery();
                /*int id = Convert.ToInt32(cmd.LastInsertedId);
                return id;*/

            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();

                }

            }
        }

        public void updateParticipantMaterial(int participantId, int materialId)
        {//when a participant returns a material, set the status to 1 to indicate that it is returned
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            connection.Open();

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE participant_material SET return_status=1 WHERE material_id=@item_id AND participant_id=@part_id";
                cmd.Parameters.AddWithValue("@item_id", materialId);
                cmd.Parameters.AddWithValue("@part_id", participantId);
                cmd.ExecuteNonQuery();
                /*int id = Convert.ToInt32(cmd.LastInsertedId);
                return id;*/

            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();

                }

            }
        }

        public DataTable getUserMaterials(int userId)//get the materials hired by a specific user
        {
            DataSet ds = new DataSet();
            conn.Open();
            MySqlCommand cmd;
            try
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select * from participant_material,material WHERE ";
                cmd.CommandText += "participant_material.material_id = material.material_id ";
                cmd.CommandText += "AND participant_material.participant_id = @userId";
                cmd.CommandText += " AND participant_material.return_status = 0";
                cmd.Parameters.AddWithValue("@userId", userId);
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception exception)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }
        #endregion
        #region entranceAppFunctions

        public DataTable getAllParticipantsFromDB() //get all participants from the participant table
        {
            try
            {
                DataSet ds = new DataSet();
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter("Select * from participant LEFT JOIN account ON participant.account_number=account.account_number", conn);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public void depositParticipantAccount(int accountNumber, double amount)
        {//update a participant's account when a deposit is made
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            connection.Open();

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE account SET balance=@amount+balance WHERE account_number=@account_number";
                cmd.Parameters.AddWithValue("@account_number", accountNumber);
                cmd.Parameters.AddWithValue("@amount", amount);
                cmd.ExecuteNonQuery();
                /*int id = Convert.ToInt32(cmd.LastInsertedId);
                return id;*/

            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();

                }

            }
        }

        public void insertAccountTransfer(int participantId, double amount)
        {//when a participant makes a deposit, insert a new record in the account_transfer table
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            connection.Open();

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO account_transfer(participant_id,amount,transfer_date)";
                cmd.CommandText += " VALUES(@part_id,@amount,@date)";
                cmd.Parameters.AddWithValue("@part_id", participantId);
                cmd.Parameters.AddWithValue("@amount", amount);
                cmd.Parameters.AddWithValue("@date", DateTime.Now);
                cmd.ExecuteNonQuery();
                /*int id = Convert.ToInt32(cmd.LastInsertedId);
                return id;*/

            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();

                }

            }
        }

        //public void changePaymentStutas(int participantId, int payment_status)
        //{//update a participant's account with the specified amount
        //    MySqlConnection connection = new MySqlConnection(connectionString);
        //    MySqlCommand cmd;
        //    connection.Open();

        //    try
        //    {
        //        cmd = connection.CreateCommand();
        //        cmd.CommandText = "UPDATE account SET payment_status=@payment_status WHERE participantId=@participantId";
        //        cmd.Parameters.AddWithValue("@participantId", participantId);
        //        cmd.Parameters.AddWithValue("@payment_status", payment_status);
        //        cmd.ExecuteNonQuery();
        //    }
        //    catch (Exception exception)
        //    {
        //        MessageBox.Show(exception.Message);
        //    }
        //    finally
        //    {

        //        if (connection.State == ConnectionState.Open)
        //        {
        //            connection.Close();

        //        }

        //    }
        //}
        #endregion

        #region eventOrganiser
        public void updateParticipantPaymentStatus(int participantId)
        {//update a participant's payment status
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            connection.Open();

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE participant SET payment_status=1 WHERE participant_id=@participantId";
                cmd.Parameters.AddWithValue("@participantId", participantId);
                cmd.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();

                }

            }
        }

        public void updateParticipantAttendanceStatus(int participantId, int status)
        {//update a participant's attendance status
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            connection.Open();

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE participant SET attendance_status=@status WHERE participant_id=@participantId";
                cmd.Parameters.AddWithValue("@participantId", participantId);
                cmd.Parameters.AddWithValue("@status", status);
                cmd.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();

                }

            }
        }

        public DataTable getAllParticipantItemsFromDB()  // get all the items from the participant_item table
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            conn.Open();
            try
            {
                DataSet ds = new DataSet();
                conn.Open();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select * from participant_item, item where participant_item.item_id = item.item_id";
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public DataTable getAmountSpentInShopsFromDB(int participantId)  // get the total amount spent by a participant in a shop
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            conn.Open();
            try
            {
                DataSet ds = new DataSet();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select SUM(item.price*quantity_purchased) as total, participant_item.participant_id from participant_item, item, participant";
                cmd.CommandText += " where participant_item.item_id = item.item_id and participant_item.participant_id=participant.participant_id ";
                cmd.CommandText += "and participant_item.participant_id = @part_id and participant.attendance_status<2 group by participant_item.participant_id";
                cmd.Parameters.AddWithValue("@part_id", participantId);
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public DataTable getAmountTransferedFromDB(int participantId)  // get the total amount transfered by a participant 
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            conn.Open();
            try
            {
                DataSet ds = new DataSet();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select SUM(amount) as total, account_transfer.participant_id as id from account_transfer,participant";
                cmd.CommandText += " where account_transfer.participant_id=participant.participant_id and participant.attendance_status<2";
                cmd.CommandText += " group by account_transfer.participant_id";
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public DataTable getSalesPerShopsFromDB()  // get the total amount sold by each shop
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            conn.Open();
            try
            {
                DataSet ds = new DataSet();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select SUM(item.price*quantity_purchased) as total, shop_id from participant_item, item";
                cmd.CommandText += " where participant_item.item_id = item.item_id group by shop_id";
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public DataTable getSalesPerItemsFromDB()  // get the total amount of each item sold 
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            conn.Open();
            try
            {
                DataSet ds = new DataSet();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select SUM(item.price*quantity_purchased) as total, item.item_id from participant_item, item";
                cmd.CommandText += " where participant_item.item_id = item.item_id group by item_id";
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public DataTable getTotalBalancesFromDB()  // get the total balance of each account 
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            conn.Open();
            try
            {
                DataSet ds = new DataSet();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select SUM(balance) as total from account";
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public DataTable getVisitorsWhoLeftFromDB()  // get the total number of visitors who left 
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            conn.Open();
            try
            {
                DataSet ds = new DataSet();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select count(*) as count from participant where attendance_status = 2";
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }
        }

        public DataTable getCurrentVisitorsFromDB()  // get the total number of current visitors i.e the status = 1
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            conn.Open();
            try
            {
                DataSet ds = new DataSet();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select count(*) as count from participant where attendance_status = 1";
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public DataTable getAllVisitorsFromDB()  // get the total number of visitors
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            conn.Open();
            try
            {
                DataSet ds = new DataSet();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select count(*) as count from participant";
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public void updateParticipantAccount(int accountNumber, double amount)
        {//update a participant's account with the specified amount
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            connection.Open();

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE account SET balance=@amount WHERE account_number=@account_number";
                cmd.Parameters.AddWithValue("@account_number", accountNumber);
                cmd.Parameters.AddWithValue("@amount", amount);
                cmd.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();

                }

            }
        }

        public DataTable getTotalShopSalesFromDB()  // get the total amount sold in all shops
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            conn.Open();
            try
            {
                DataSet ds = new DataSet();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select SUM(item.price*quantity_purchased) as total from participant_item, item";
                cmd.CommandText += " where participant_item.item_id = item.item_id";
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }

        }

        public DataTable getTotalStandSalesFromDB()  // get the total amount spent in all stands
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd;
            conn.Open();
            try
            {
                DataSet ds = new DataSet();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select SUM(material.price*quantity_hired) as total from participant_material, material";
                cmd.CommandText += " where participant_material.material_id = material.material_id AND return_status = 0";
                cmd.ExecuteNonQuery();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }
        }
        #endregion
       
    }
}

   
